package com.easyui.common;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.easyui.DB.ConnectDB;
import com.easyui.DB.OperateDB;
import com.easyui.bean.Project;
import com.easyui.bean.Student;

public class SelProject {
	
	/**
	 * 得到初始化后的Project对象
	 * @param pname
	 * @param stimef
	 * @param stimet
	 * @param progress
	 * @param etimef
	 * @param etimet
	 * @return
	 */
	public Project getProject(String pname, String pubname, String stimef, String stimet, String progress, String etimef, String etimet) {
		Project p = new Project(pname, pubname, stimef, stimet, progress, etimef, etimet);
		return p;
	}
	
	/*public Project getProject() {
		Project p = new Project();
		return p;
	}*/
	
	/**
	 * 添加新项目，插入数据库有关信息
	 * @param tno
	 * @param pname
	 * @param etime
	 * @param pdetail
	 * @param member
	 * @param attachFile
	 * @return
	 */
	public boolean addProject(String tno, String pname, String etime, String pdetail, List<String> member, List<Map> attachFile) {
		boolean success = true;
		
		Connection conn = ConnectDB.getConnection();
		boolean autoCommit = false;
		try {
			autoCommit = conn.getAutoCommit();
			conn.setAutoCommit(false);// 取消自动提交
			
			boolean attach = false;//是否有附件
			if(attachFile != null && attachFile.size() > 0) {
				attach = true;
			} else {
				attach = false;
			}
			
			//向project插入数据
			int pno = newProject(conn, tno, pname, etime, pdetail, attach);
			
			if(pno != -1) {
				//向partake插入数据
				if(newPartake(conn, pno, member)) {
					if(attach) {//有附件
						//向attachment表插入数据.
						System.out.println("有附件");
						if(new SelAttach().newAttach(conn, tno, pno, pno, "teacher", "project", attachFile)) {
							success = true;
						} else {
							success = false;
						}
					} else {
						success = true;
					}
				} else {
					success = false;
				}
			} else {
				success = false;
			}
			
		} catch (SQLException e) {
			success = false;
			System.out.println("添加项目失败，详情如下：\n");
			e.printStackTrace();
		} catch (NumberFormatException e) {
			success = false;
			System.out.println("数据格式化异常，详情如下：\n");
			e.printStackTrace();
		} finally {
			try {
				if (success) {
					conn.commit();//手动提交
				} else {
					conn.rollback();//回滚
				}
				conn.setAutoCommit(autoCommit);// 恢复现场
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			OperateDB.close(conn);
		}
		
		System.out.println("add:" + success);
		
		return success;
	}
	
	/**
	 * 修改项目信息
	 * @param tno
	 * @param pno
	 * @param pname
	 * @param addtaker
	 * @param etime
	 * @param pdetail
	 * @param progress
	 * @param stage
	 * @return
	 */
	public boolean modifyProject(String tno, int pno, String pname, String []addtaker, String etime, String pdetail, String[] stage) {
		boolean success = true;
		
		Connection conn = ConnectDB.getConnection();
		boolean autoCommit = false;
		try {
			autoCommit = conn.getAutoCommit();
			conn.setAutoCommit(false);// 取消自动提交
			
			
			//向project更新数据
			if(updateProject(conn, pno, pname, etime, pdetail)) {
				//更新partake状态
				if(updateStage(conn, pno, stage)) {
					if(addtaker != null && addtaker.length > 0) {
						//向partake插入数据
						if(addPartake(conn, pno, addtaker)) {
							success = true;
						} else {
							success = false;
						}
					} 
				} else {
					success = false;
				}
			} else {
				success = false;
			}
			
		} catch (SQLException e) {
			success = false;
			System.out.println("修改项目失败，详情如下：\n");
			e.printStackTrace();
		} catch (NumberFormatException e) {
			success = false;
			System.out.println("数据格式化异常，详情如下：\n");
			e.printStackTrace();
		} finally {
			try {
				if (success) {
					conn.commit();//手动提交
				} else {
					conn.rollback();//回滚
				}
				conn.setAutoCommit(autoCommit);// 恢复现场
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			OperateDB.close(conn);
		}
		
		System.out.println("add:" + success);
		
		return success;
	}
	
	public boolean updateProject(Connection conn, int pno, String pname, String etime, String pdetail){
		boolean success = false;
		List<Object> plist = null;
		
		if(pno != -1) {
			String	sql = "update project set pname = ?, etime = ?, pcont = ? where pno = ?";
System.out.println(sql);
			plist = new ArrayList<Object>();	
			plist.add(pname);
			plist.add(etime);
			plist.add(pdetail);
			plist.add(pno);
			OperateDB.setParmList(plist);//初始化paramList
			
			success = OperateDB.update(conn, sql);
		} else {
			success = false;
		}
		
		return success;
	}
	
	/**
	 * 返回插入的项目编号
	 * @return
	 */
	public int newProject(Connection conn, String tno, String pname, String etime, String pdetail, boolean attach) {
		int pno = -1;
		
		List<Object> plist = null;
		Map<String, Object> m = null;
		
		String stime = "";
		String isAttach = "";
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		stime = sdf.format(new java.util.Date());
		if(attach) {
			isAttach = "1";
		} else {
			isAttach = "0";
		}
		
		String sql = "insert into project values(null, ?, ?, ?, ?, ?, '0', '1', ?)";
		plist = new ArrayList<Object>();
		plist.add(pname);
		plist.add(pdetail);
		plist.add(stime);
		plist.add(etime);
		plist.add(tno);
		plist.add(isAttach);
		OperateDB.setParmList(plist);
		
		m = OperateDB.insertAndGet(conn, sql);
		if(m != null && m.size() > 0) {
			boolean success = (Boolean)m.get("isSuccess");
			pno = (Integer)m.get("generatedKey");
			System.out.println(pno);
		}
		
		return pno;
	}
	
	/**
	 * 向partake表插入数据
	 * @param conn
	 * @param pno
	 * @param member
	 * @return
	 */
	public boolean newPartake(Connection conn, int pno, List<String> member) {
		boolean success = false;
		List<Object> plist = null;
		
		if(member != null && member.size() > 0) {
			for (int i = 0; i < member.size(); i++) {
					plist = new ArrayList<Object>();
					String sno = member.get(i);
					String sql = "insert into partake values(?, ?, 'todo')";
					plist.add(sno);
					plist.add(pno);
					
					OperateDB.setParmList(plist);//初始化paramList
					
					if(!OperateDB.update(conn,sql)) {
						success = false;
						break; // 跳出循环
					} else {
						System.out.println("insert partake " + sno + "success.");
						success = true;
					}
				}
			
		} else {
			success = false;
		}
		
		System.out.println("partake:" + success);
		
		return success;
	}
	
	public boolean addPartake(Connection conn, int pno, String[] addtaker) {
		boolean success = false;
		List<Object> plist = null;
		
		if(addtaker != null && addtaker.length > 0) {
			for (int i = 0; i < addtaker.length; i++) {
					plist = new ArrayList<Object>();
					String sno = addtaker[i];
					String sql = "insert into partake values(?, ?, 'todo')";
					plist.add(sno);
					plist.add(pno);
					
					OperateDB.setParmList(plist);//初始化paramList
					
					if(!OperateDB.update(conn,sql)) {
						success = false;
						break; // 跳出循环
					} else {
						System.out.println("insert partake " + sno + "success.");
						success = true;
					}
				}
			
		} else {
			success = false;
		}
		
		System.out.println("partake:" + success);
		
		return success;
	}
	
	public boolean updateStage(Connection conn, int pno, String[] stage) {
		boolean success = false;
		List<Object> plist = null;
		
		if(stage != null && stage.length > 0) {
			for (int i = 0; i < stage.length; i++) {
					plist = new ArrayList<Object>();
					String []ss = stage[i].split(",");
					String sno = ss[0];
				System.out.println("sno:" + sno);
					String sta = ss[1];
				System.out.println("sno:" + sta);
					String sql = "update partake set stage = ? where pno = ? and sno = ?";
					plist.add(sta);
					plist.add(pno);
					plist.add(sno);
					
					OperateDB.setParmList(plist);//初始化paramList
					
					if(!OperateDB.update(conn,sql)) {
						success = false;
						break; // 跳出循环
					} else {
						System.out.println("update partake " + sno + "success.");
						success = true;
					}
				}
			
		} else {
			success = false;
		}
		
		System.out.println("partake:" + success);
		
		return success;
	}
	
	/**
	 * 获取所有项目数据
	 * 返回Map类型的链表
	 * @param orderBy
	 * @param sortName
	 * @return List
	 */
	public List<Map> getTOwnProjects(String tno, String orderBy, String sortName, Project p) {
		List<Map> initList = null;
		List<Object> plist = null;
		
System.out.println(orderBy);
System.out.println(sortName);
		
		String addSql = addWhere(p);
		String sql = "";
		if(addSql != null && !addSql.equals("")) {
			sql = "select * from project where pubno = '" + tno + "' " + addSql + " order by " + sortName + " " + orderBy;
		} else {
			sql = "select * from project where pubno = '" + tno + "' order by " + sortName + " " + orderBy;
		}

System.out.println(sql);
		OperateDB.setParmList(plist);//初始化paramList
		initList = OperateDB.excuteQuery(sql);
		
		return initList;
	}
	
	/**
	 * 获得学生参与的项目
	 * @param sno
	 * @param orderBy
	 * @param sortName
	 * @param p
	 * @return
	 */
	public List<Map> getSProjects(String sno, String orderBy, String sortName, Project p) {
		List<Map> initList = null;
		List<Object> plist = null;
		
System.out.println(orderBy);
System.out.println(sortName);
		
		String addSql = addWhere(p);
		String sql = "";
		if(addSql != null && !addSql.equals("")) {
			sql = "select * from project p, partake pt, teacher t where p.pubno = t.tno and p.pno = pt.pno and pt.sno = '" + sno + "' " + addSql + " order by " + sortName + " " + orderBy;
		} else {
			sql = "select * from project p, partake pt, teacher t where p.pubno = t.tno and p.pno = pt.pno and pt.sno = '" + sno + "' order by " + sortName + " " + orderBy;
		}

System.out.println(sql);
		OperateDB.setParmList(plist);//初始化paramList
		initList = OperateDB.excuteQuery(sql);
		
		return initList;
	}
	
	public List<Map> getSProjects(String sno) {
		List<Map> initList = null;
		List<Object> plist = null;
		
		String sql = "";
		sql = "select * from project p, partake pt, teacher t where p.pubno = t.tno and p.pno = pt.pno and pt.sno = '" + sno + "' order by p.pno desc";

System.out.println(sql);
		OperateDB.setParmList(plist);//初始化paramList
		initList = OperateDB.excuteQuery(sql);
		
		return initList;
	}
	
	/**
	 * 获得老师自己发布的项目
	 * 不实现分页
	 * @param tno
	 * @return
	 */
	public List<Map> getTProjects(String tno) {
		List<Map> initList = null;
		List<Object> plist = null;
		
		String sql = "";
		sql = "select * from project where pubno = ?";

System.out.println(sql);
		plist = new ArrayList<Object>();
		plist.add(tno);
		OperateDB.setParmList(plist);//初始化paramList
		initList = OperateDB.excuteQuery(sql);
		
		return initList;
	}
	
	/**
	 * 更改学生指定项目的状态
	 * @param pno
	 * @param sno
	 * @param changeTo
	 * @return
	 */
	public boolean changeStage(int pno, String sno, String changeTo){
		boolean success = false;
		List<Object> plist = null;
		
		if(pno != -1) {
			String	sql = "update partake set stage = ? where sno = ? and pno = ?";

			plist = new ArrayList<Object>();	
			plist.add(changeTo);
			plist.add(sno);
			plist.add(pno);
			OperateDB.setParmList(plist);//初始化paramList
			
			success = OperateDB.update(sql);
		} else {
			success = false;
		}
		
		return success;
	}
	
	public Map<String, Object> changeProgress(int pno, String tno, int action){
		Map<String, Object> result = new HashMap<String, Object>();
		boolean success = false;
		String msg = "";
		List<Object> plist = null;
		
		if(pno != -1) {
			if(canProChange(pno, tno, action)) {
				String	sql = "update project set progress = ? where pubno = ? and pno = ?";
				plist = new ArrayList<Object>();	
				plist.add(action);
				plist.add(tno);
				plist.add(pno);
				OperateDB.setParmList(plist);//初始化paramList
				
				success = OperateDB.update(sql);
			} else {
				success = false;
				msg = "更改项目进度失败，请刷新后再试.";
			}
		} else {
			success = false;
			msg = "更改项目进度失败.";
		}
		
		if(success) {
			msg = "更改项目进度成功.";
		}
		
		result.put("success", success);
		result.put("msg", msg);
		return result;
	}
	
	public boolean canProChange(int pno, String tno, int action) {
		boolean success = false;
		List<Object> plist = null;
		List<Map> initList = null;
		
		if(pno != -1) {
			String progress = "";
			if(action == 1) {
				progress = "0";
			} else {
				progress = "1";
			}
			
			String	sql = "select * from project where pno = ? and pubno = ? and progress = ?";

			plist = new ArrayList<Object>();	
			plist.add(pno);
			plist.add(tno);
			plist.add(progress);
			OperateDB.setParmList(plist);//初始化paramList
			
			initList = OperateDB.excuteQuery(sql);
			if(initList != null && initList.size() > 0) {
				success = true;
			} else {
				success = false;
			}
		} else {
			success = false;
		}
		
		return success;
	}
	
	public List<Map> getTAllProjects(String orderBy, String sortName, Project p) {
		List<Map> initList = null;
		List<Object> plist = null;
		
System.out.println(orderBy);
System.out.println(sortName);
		
		String addSql = addWhere(p);
		String sql = "";
		if(addSql != null && !addSql.equals("")) {
			sql = "select * from project p, teacher t where p.pubno=t.tno " + addSql + " order by " + sortName + " " + orderBy;
		} else {
			sql = "select * from project p, teacher t where p.pubno=t.tno order by " + sortName + " " + orderBy;
		}

System.out.println(sql);
		OperateDB.setParmList(plist);//初始化paramList
		initList = OperateDB.excuteQuery(sql);
		
		return initList;
	}
	
	/**
	 * 获得项目详细的信息
	 * @param pno
	 * @return
	 */
	public List<Project> getProjectDetail(int pno) {
		List<Project> pdList = null;
		List<Map> finalList = null;
		List<Object> plist = null;
		
		String sql = "select * from project p, teacher t where p.pubno = t.tno and pno = ? ";
		System.out.println(sql);
		plist = new ArrayList<Object>();	
		plist.add(pno);
		OperateDB.setParmList(plist);//初始化paramList
			
		finalList = OperateDB.excuteQuery(sql);
			
		if(finalList != null && finalList.size() > 0) {
			pdList = new ArrayList<Project>();
			for(int i=0; i<finalList.size(); i++) {
				Map map = finalList.get(i);
				Project p = new Project();
				p.init(map);
				p.initDetail(map);
				pdList.add(p);
			}
		}
		
		return pdList;
	}
	
	/**
	 * 获得指定工号的教师发布的项目
	 * 返回指定页码和条件的项目链表
	 * @param initList
	 * @param tno
	 * @param page
	 * @param rows
	 * @param orderBy
	 * @param sortName
	 * @param p
	 * @return
	 */
	public List<Project> getTOSpeProjects(List<Map> initList, String tno, int page, int rows, String orderBy, String sortName, Project p) {
		List<Project> prlist = null;
		List<Map> finalList = null;
		List<Object> plist = null;
		
		if(initList != null && initList.size() > 0) {//有学生信息时
			int totalRecords = 0;
			int totalPage = 0;
			int pageSize = rows;//rows 一页显示几行
			int pageNo = page;//page 第几页
			
			totalRecords = initList.size();
			totalPage = (totalRecords + pageSize -1)/pageSize;
			if(totalPage < 1) {
				totalPage = 1;
			}
			int startPos = (pageNo -1) * pageSize;
			
			String addSql = addWhere(p);
			String sql = "";
			if(addSql != null && !addSql.equals("")) {
				sql = "select * from project p, teacher t where p.pubno = t.tno and p.pubno = '" + tno + "' " + 
						addSql + " order by " + sortName + " " + orderBy + 
						" limit " + startPos + ", " + pageSize;
			} else {
				sql = "select * from project p, teacher t where p.pubno = t.tno and p.pubno = '" + tno + 
						"' order by " + sortName + " " + orderBy + 
						" limit " + startPos + ", " + pageSize;
			}
			
			
System.out.println(sql);
			OperateDB.setParmList(plist);//初始化paramList
			finalList = OperateDB.excuteQuery(sql);
			
			if(finalList != null && finalList.size() > 0) {//有学生信息时
				prlist = new ArrayList<Project>();
				for(int i=0; i<finalList.size(); i++) {
					Map map = finalList.get(i);
					Project pr = new Project();
					pr.init(map);
					prlist.add(pr);
				}
			}
		}
		
		return prlist;
	}
	
		public List<Project> getSSpeProjects(List<Map> initList, String sno, int page, int rows, String orderBy, String sortName, Project p) {
		List<Project> prlist = null;
		List<Map> finalList = null;
		List<Object> plist = null;
		
		if(initList != null && initList.size() > 0) {//
			int totalRecords = 0;
			int totalPage = 0;
			int pageSize = rows;//rows 一页显示几行
			int pageNo = page;//page 第几页
			
			totalRecords = initList.size();
			totalPage = (totalRecords + pageSize -1)/pageSize;
			if(totalPage < 1) {
				totalPage = 1;
			}
			int startPos = (pageNo -1) * pageSize;
			
			String addSql = addWhere(p);
			String sql = "";
			if(addSql != null && !addSql.equals("")) {
				sql = "select * from project p, partake pt, teacher t where p.pubno = t.tno and p.pno = pt.pno and pt.sno = '" + sno + "' " + 
						addSql + " order by " + sortName + " " + orderBy + 
						" limit " + startPos + ", " + pageSize;
			} else {
				sql = "select * from project p, partake pt, teacher t where p.pubno = t.tno and p.pno = pt.pno and pt.sno = '" + sno + 
						"' order by " + sortName + " " + orderBy + 
						" limit " + startPos + ", " + pageSize;
			}
			
			
System.out.println(sql);
			OperateDB.setParmList(plist);//初始化paramList
			finalList = OperateDB.excuteQuery(sql);
			
			if(finalList != null && finalList.size() > 0) {
				prlist = new ArrayList<Project>();
				for(int i=0; i<finalList.size(); i++) {
					Map map = finalList.get(i);
					Project pr = new Project();
					pr.initSP(map);
					prlist.add(pr);
				}
			}
		}
		
		return prlist;
	}
	
	public List<Project> getTAllSpeProjects(List<Map> initList, int page, int rows, String orderBy, String sortName, Project p) {
		List<Project> prlist = null;
		List<Map> finalList = null;
		List<Object> plist = null;
		
		if(initList != null && initList.size() > 0) {//有学生信息时
			int totalRecords = 0;
			int totalPage = 0;
			int pageSize = rows;//rows 一页显示几行
			int pageNo = page;//page 第几页
			
			totalRecords = initList.size();
			totalPage = (totalRecords + pageSize -1)/pageSize;
			if(totalPage < 1) {
				totalPage = 1;
			}
			int startPos = (pageNo -1) * pageSize;
			
			String addSql = addWhere(p);
			String sql = "";
			if(addSql != null && !addSql.equals("")) {
				sql = "select * from project p, teacher t where p.pubno = t.tno " + 
						addSql + " order by " + sortName + " " + orderBy + 
						" limit " + startPos + ", " + pageSize;
			} else {
				sql = "select * from project p, teacher t where p.pubno = t.tno " +  
						" order by " + sortName + " " + orderBy + 
						" limit " + startPos + ", " + pageSize;
			}
			
			
System.out.println(sql);
			OperateDB.setParmList(plist);//初始化paramList
			finalList = OperateDB.excuteQuery(sql);
			
			if(finalList != null && finalList.size() > 0) {//有学生信息时
				prlist = new ArrayList<Project>();
				for(int i=0; i<finalList.size(); i++) {
					Map map = finalList.get(i);
					Project pr = new Project();
					pr.init(map);
					prlist.add(pr);
				}
			}
		}
		
		return prlist;
	}
	
	/**
	 * 获得指定页及每页大小的项目信息并按照指定字段排序
	 * 返回Project类型的链表
	 * @param page
	 * @param rows
	 * @param orderBy
	 * @param sortName
	 * @return
	 */
	public List<Project> getSpeProjects(List<Map> initList, int page, int rows, String orderBy, String sortName) {
		List<Project> prlist = null;
		List<Map> finalList = null;
		List<Object> plist = null;
		
		if(initList != null && initList.size() > 0) {//有学生信息时
			int totalRecords = 0;
			int totalPage = 0;
			int pageSize = rows;//rows 一页显示几行
			int pageNo = page;//page 第几页
			
			totalRecords = initList.size();
			totalPage = (totalRecords + pageSize -1)/pageSize;
			if(totalPage < 1) {
				totalPage = 1;
			}
			int startPos = (pageNo -1) * pageSize;
			
			/*String addSql = addWhere(at);
			String sql = "";
			if(addSql != null && !addSql.equals("")) {
				sql = "select * from teacher where 1=1 " + addSql + " order by " + sortName + " " + orderBy + 
				" limit " + startPos + ", " + pageSize;
			} else {
				sql = "select * from teacher order by " + sortName + " " + orderBy + 
				" limit " + startPos + ", " + pageSize;
			}*/
			
			String sql = "select * from project order by " + sortName + " " + orderBy + 
					" limit " + startPos + ", " + pageSize;
			
System.out.println(sql);
			OperateDB.setParmList(plist);//初始化paramList
			finalList = OperateDB.excuteQuery(sql);
			
			if(finalList != null && finalList.size() > 0) {//有学生信息时
				prlist = new ArrayList<Project>();
				for(int i=0; i<finalList.size(); i++) {
					Map map = finalList.get(i);
					Project p = new Project();
					p.init(map);
					prlist.add(p);
				}
			}
		}
		
		return prlist;
	}
	
	/**
	 * 删除指定教师的指定项目
	 * @param id
	 * @return
	 */
	public Map<String, Object> deleteOwnProject(String[] id, String tno) {
		Map<String, Object> result = new HashMap<String, Object>();
		List<Object> plist = null;
		boolean success = true;
		String msg = "";
		
		if (id.length < 1) {
			success = false;
			msg = "未选择删除数据.";
		} else {
			Connection conn = ConnectDB.getConnection();
			boolean autoCommit = false;
			try {
				autoCommit = conn.getAutoCommit();
				conn.setAutoCommit(false);// 取消自动提交
				
				for (int i = 0; i < id.length; i++) {
					if(canDelProject(conn, tno, id[i])) {	//是否可以删除,表project中是否还有信息
						if(!deleteOwnProject(conn, id[i])) {//删除项目信息,表attachment,note,partake,project
							success = false;
							msg = "删除项目失败.";
							break; // 跳出循环
						} 
					} else {
						success = false;
						msg = "项目未完成，不能删除.";
						break; // 跳出循环
					}
				}

			} catch (SQLException e) {
				success = false;
				System.out.println("删除数据失败，详情如下：\n");
				e.printStackTrace();
			} catch (NumberFormatException e) {
				success = false;
				System.out.println("数据格式化异常，详情如下：\n");
				e.printStackTrace();
			} finally {
				try {
					if (success) {
						conn.commit();//手动提交
					} else {
						conn.rollback();//回滚
					}
					conn.setAutoCommit(autoCommit);// 恢复现场
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
				OperateDB.close(conn);
			}
		}
		
		if(success) {
			msg = "删除项目成功";
		}

		result.put("success", success);
		result.put("msg", msg);
		
		return result;
	}
	
	/**
	 * 删除项目信息
	 * 表attachment,note,partake,project
	 * @param conn
	 * @param tno
	 * @param pno
	 * @return
	 */
	public boolean deleteOwnProject(Connection conn, String pno) {
		List<Object> plist = null;
		boolean success = false;
		String sql = "";
		
		//删除attachment表中信息，可能没有相关信息
		sql = "delete from attachment where pno = ?";
		plist = new ArrayList<Object>();	
		plist.add(pno);
		OperateDB.setParmList(plist);//初始化paramList
		
		if(!OperateDB.update(conn, sql)) {
			success = false;
		} else {
			//删除note表中信息
			sql = "delete from note where pno = ?"; 
			plist = new ArrayList<Object>();	
			plist.add(pno);
			OperateDB.setParmList(plist);//初始化paramList
			
			if(!OperateDB.update(conn, sql)) {
				success = false;
			} else {
				//删除partake表中信息
				sql = "delete from partake where pno = ?";
				plist = new ArrayList<Object>();	
				plist.add(pno);
				OperateDB.setParmList(plist);//初始化paramList
				
				if(!OperateDB.update(conn, sql)) {
					success = false;
				} else {
					//删除project表中信息
					sql = "delete from project where pno = ?";
					plist = new ArrayList<Object>();	
					plist.add(pno);
					OperateDB.setParmList(plist);//初始化paramList
					
					success = OperateDB.update(conn, sql);
				}
			}
		}
		
		return success;
	}
	
	/**
	 * 指定教师能否删除指定项目
	 * @param conn
	 * @param tno
	 * @param sno
	 * @return
	 */
	public boolean canDelProject(Connection conn, String tno, String pno) {
		List<Object> plist = null;
		boolean canDel = false;
		
		String sql = "";
		
		//proejct表,存在该教师发布的项目并且状态为已完成
		sql = "select * from project where pno = ? and pubno = ? and progress = ?";
		plist = new ArrayList<Object>();	
		plist.add(pno);
		plist.add(tno);
		plist.add("1");
		OperateDB.setParmList(plist);//初始化paramList
		
		List<Map> parojectList = null;
		parojectList = OperateDB.excuteQuery(conn, sql);
		
		if(parojectList != null && parojectList.size() > 0) {//存在则可删除
			canDel = true;
		} else {		
			canDel = false;
		}
			
		return canDel;
	}
	
	/**
	 * 增加查询条件
	 * @param p
	 * @return
	 */
	public String addWhere(Project p) {
		String addSql = "";
		String pname = p.getPname();
		String pubname = p.getPubname();
		String stimef = p.getStimef();
		String stimet = p.getStimet();
		String progress = p.getProgress();
		String etimef = p.getEtimef();
		String etimet = p.getEtimet();
		
		if(pname != null && !pname.equals("")) {
			addSql += " and pname like  '%" +  pname + "%' ";
		}
		
		if(pubname != null && !pubname.equals("")) {
			addSql += " and tname like  '%" +  pubname + "%' ";
		}
		
		if(progress != null && !progress.equals("")) {
			addSql += " and progress = '" +  progress + "'";
		}
		
		if(stimef != null && !stimef.equals("")) {
			if(stimet != null && !stimet.equals("")) {//两个都有值
				addSql += " and stime between '" + stimef + "' and '" + stimet + "' ";
			} else {//stimef有值，stimet没有值
				addSql += " and stime >= '" + stimef + "' ";
			}
		} else {
			if(stimet != null && !stimet.equals("")) {//stimef没有值，stimet有值
				addSql += " and stime <= '" + stimet + "' ";
			} //else 两个都没有值
		}
		
		if(etimef != null && !etimef.equals("")) {
			if(etimet != null && !etimet.equals("")) {//两个都有值
				addSql += " and etime between '" + etimef + "' and '" + etimet + "' ";
			} else {//etimef有值，etimet没有值
				addSql += " and etime >= '" + etimef + "' ";
			}
		} else {
			if(etimet != null && !etimet.equals("")) {//stimef没有值，stimet有值
				addSql += " and etime <= '" + etimet + "' ";
			} //else 两个都没有值
		}
		
		return addSql;
	}
	
	/**
	 * 获取指定学生参与的所有项目编号
	 * 返回一个链表
	 * @param sno
	 * @param pno
	 * @return
	 */
	public List<Integer> getProList(String sno) {
		List<Integer> proNumList = null;
		List<Map> finalList = null;
		List<Object> plist = null;
		
		String sql = "select * from partake where sno = ? ";
		System.out.println(sql);
		plist = new ArrayList<Object>();	
		plist.add(sno);
		OperateDB.setParmList(plist);//初始化paramList
			
		finalList = OperateDB.excuteQuery(sql);
			
		if(finalList != null && finalList.size() > 0) {//有学生信息时
			proNumList = new ArrayList<Integer>();
			for(int i=0; i<finalList.size(); i++) {
				Map map = finalList.get(i);
				int pno = (Integer)map.get("pno");
				proNumList.add(pno);
			}
		}
		
		return proNumList;
	}
	
	/**
	 * 获取指定学生参与指定教师的项目编号
	 * 返回一个链表
	 * @param sno
	 * @param pno
	 * @return
	 */
	public List<Integer> getSpeProList(String sno, String tno) {
		List<Integer> proNumList = null;
		List<Map> finalList = null;
		List<Object> plist = null;
		
		String sql = "select * from partake " + 
						" where sno = ? " + 
						" and pno in " + 
						" (select pno from project where pubno = ?) ";
		
		plist = new ArrayList<Object>();	
		plist.add(sno);
		plist.add(tno);
		OperateDB.setParmList(plist);//初始化paramList
			
		finalList = OperateDB.excuteQuery(sql);
			
		if(finalList != null && finalList.size() > 0) {//有学生信息时
			proNumList = new ArrayList<Integer>();
			for(int i=0; i<finalList.size(); i++) {
				Map map = finalList.get(i);
				int pno = (Integer)map.get("pno");
				proNumList.add(pno);
			}
		}
		
		return proNumList;
	}
}
